using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.ComponentModel;
using Common.Utilities;

namespace Workflows.Components.Reports
{
    public class NumberFieldAggregate : AggregateBase
    {
        private decimal _Min;
        private decimal _Max;
        private decimal _Mean;
        private decimal _Sum;
        private decimal _Median;
        private decimal _SD;
        private Dictionary<Range<decimal>, int> _Spread;

        public NumberFieldAggregate(string connStr, string tblName, string colName)
            : base(connStr, tblName, colName)
        {
            this.SummarizeData(connStr, tblName, colName);
        }

        private void SummarizeData(string connStr, string tblName, string colName)
        {
            if (this.Type != AggregateDataType.Number)
            {
                throw new Exception("This class only supports number field type");
            }
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select min ([" + colName + "]) as NumberFrom, " +
                    "max([" + colName + "]) as NumberTo from [" + tblName + "] " +
                    "where [" + colName + "] is not null";
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    try
                    {
                        this._Min = decimal.Parse(reader[0].ToString());
                        this._Max = decimal.Parse(reader[1].ToString());
                    }
                    catch
                    {
                        this._Max = 0;
                        this._Min = 0;
                    }
                }
                reader.Close();
                try
                {
                    cmd.CommandText = "select sum([" + colName + "]) as SubTotal, " +
                        "avg([" + colName + "]) as Mean from [" + tblName + "] " +
                        "where [" + colName + "] is not null";
                    reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        this._Sum = decimal.Parse(reader[0].ToString());
                        this._Mean = decimal.Parse(reader[1].ToString());
                    }
                    reader.Close();
                }
                catch
                {
                    reader.Close();
                    this._Sum = 0;
                    this._Mean = 0;
                }
                decimal sumSq = 0;
                int total = 0;
                cmd.CommandText = "select [" + colName + "] from [" + tblName + "] " +
                    "where [" + colName + "] is not null order by [" + colName + "]";
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    decimal value = decimal.Parse(reader[0].ToString());
                    if (total == this.Count / 2)
                    {
                        this._Median = value;
                    }
                    sumSq += (value - this.Mean) * (value - this.Mean);
                    total++;
                }
                reader.Close();
                if (total > 0)
                {
                    this._SD = (decimal)Math.Sqrt((double)sumSq / total);
                }
                else
                {
                    this._SD = 0;
                }

                this._Spread = new Dictionary<Range<decimal>, int>();
                if (this.Count > 12)
                {
                    cmd.CommandText = "select count ([" + colName + "]) as CountByKey, [" +
                        colName + "] from [" + tblName + "] where [" + colName + "] is not null " +
                        "group by ([" + colName + "])";
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        int count = reader.GetInt32(0);
                        decimal numberKey = decimal.Parse(reader[1].ToString());
                        Range<decimal> range = new Range<decimal>(numberKey, numberKey);
                        this._Spread.Add(range, count);
                    }
                    reader.Close();
                }
                else
                {
                    if (total > 0 && this._Max > this._Min)
                    {
                        decimal interval = (this._Max - this._Min) / 10;
                        for (int i = 0; i < 10; i++)
                        {
                            decimal rangeFrom = this._Min + i * interval;
                            decimal rangeTo = this._Min + (i + 1) * interval;
                            Range<decimal> range = new Range<decimal>(rangeFrom, rangeTo);
                            if (i < 9)
                            {
                                cmd.CommandText = "select count (*) from [" + tblName + "] where [" +
                                    colName + "]>=" + range.FromValue + " and [" + colName +
                                    "]<" + range.ToValue;
                            }
                            else
                            {
                                cmd.CommandText = "select count (*) from [" + tblName + "] where [" +
                                    colName + "]>=" + range.FromValue + " and [" + colName +
                                    "]<=" + range.ToValue;
                            }
                            reader = cmd.ExecuteReader();
                            int count = 0;
                            if (reader.Read())
                            {
                                try
                                {
                                    count = reader.GetInt32(0);
                                }
                                catch
                                {
                                    count = 0;
                                }
                            }
                            reader.Close();
                            this.Spread.Add(range, count);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        #region properties
        [Browsable(true), Category("Stats")]
        public decimal Min
        {
            get
            {
                return this._Min;
            }
        }

        [Browsable(true), Category("Stats")]
        public decimal Max
        {
            get
            {
                return this._Max;
            }
        }

        [Browsable(true), Category("Stats")]
        public decimal Mean
        {
            get
            {
                return this._Mean;
            }
        }

        [Browsable(true), Category("Stats")]
        public decimal Median
        {
            get
            {
                return this._Median;
            }
        }

        [Browsable(true), Category("Stats")]
        public decimal Sum
        {
            get
            {
                return this._Sum;
            }
        }

        [Browsable(true), Category("Stats")]
        public decimal SD
        {
            get
            {
                return this._SD;
            }
        }
        [Browsable(true), Category("Stats")]
        public Dictionary<Range<decimal>, int> Spread
        {
            get { return _Spread; }
        }

        [Browsable(true), Category("Stats")]
        public new decimal Mode
        {
            get
            {
                try
                {
                    return decimal.Parse(base.Mode);
                }
                catch
                {
                    return 0;
                }
            }
        }
        #endregion
    }
}
